<?php

function quote($string){
	return "'" . str_replace("'", "''", $string) . "'";
}

class Survey extends AppModel{
	var $name = 'Survey';
	var $belongsTo = 'Client';
	var $hasMany = array('Item'=>array('dependent'=>true), 'Cronjob'=>array('dependent'=>true), 'Rectangle'=>array('dependent'=>true));
	var $hasAndBelongsToMany = array('User'=>array('joinTable'=>'surveys_users', 'with'=>'SurveysUser'));
	
	function survey_sql($id){
		$survey = $this->findById($id);
		$sql = "INSERT INTO surveys(id, name, run_immed, results_buffer) VALUES(";
		$sql .= $survey['Survey']['id'] . ", ";
		$sql .= quote($survey['Survey']['name']) . ", ";
		$sql .= $survey['Survey']['run_immed'] . ", ";
		$sql .= $survey['Survey']['results_buffer'] . ");\n";
		return $sql;
	}
	
	function items_sql($id){
		$survey = $this->findById($id);
		$sql = '';
		foreach($survey['Item'] as $item){
			$sql .= "INSERT INTO items(id, survey_id, prompt, returns, next) VALUES(";
			$sql .= $item['id'] . ", ";
			$sql .= $item['survey_id'] . ", ";
			$sql .= quote($item['prompt']) . ", ";
			$sql .= quote($item['returns']) . ", ";
			$sql .= quote($item['next']) . ");\n";
		}
		return $sql;
	}
	
	function cronjobs_sql($id){
		$survey = $this->findById($id);
		$sql = '';
		foreach($survey['Cronjob'] as $cronjob){
			$sql .= "INSERT INTO cronjobs(id, survey_id, action, mon, dom, dow, hour, min) VALUES(";
			$sql .= $cronjob['id'] . ", ";
			$sql .= $cronjob['survey_id'] . ", ";
			$sql .= $cronjob['action'] . ", ";
			$sql .= quote($cronjob['mon']) . ", ";
			$sql .= quote($cronjob['dom']) . ", ";
			$sql .= quote($cronjob['dow']) . ", ";
			$sql .= quote($cronjob['hour']) . ", ";
			$sql .= quote($cronjob['min']) . ");\n";
		}
		return $sql;
	}
	
	function results_sql($id){
		$survey = $this->findById($id);
		$sql = "CREATE TABLE results" . $survey['Survey']['id'];
		$sql .= "( user_id INTEGER, time TIMESTAMP";
		$not_first = false;
		foreach($survey['Item'] as $item){
			$returns = explode(',', $item['returns']);
			foreach($returns as $index=>$return){
				$sql .= ", q" . $item['id'] . '_' . $index;
				switch((int)$return){
					case 0:
						$sql .= " INTEGER";
						break;
					case 1: case 2: case 3: case 4:
						$sql .= " TEXT";
				}
			}
		}
		$sql .= ");\n";
		return $sql;
	}
	
	function rectangles_sql($id){
		$survey = $this->findById($id);
		$sql = '';
		foreach($survey['Rectangle'] as $rectangle){
			$sql .= "INSERT INTO rectangles(id, item_id, pointx, pointy, width, height, pic_width, pic_height, name) VALUES(";
			$sql .= $rectangle['id'] . ", ";
			$sql .= $rectangle['item_id'] . ", ";
			$sql .= $rectangle['pointx'] . ", ";
			$sql .= $rectangle['pointy'] . ", ";
			$sql .= $rectangle['width'] . ", ";
			$sql .= $rectangle['height'] . ", ";
			$sql .= $rectangle['pic_width'] . ", ";
			$sql .= $rectangle['pic_height'] . ", ";
			$sql .= quote($rectangle['name']) . ");\n";
		}
		return $sql;
	}
			
	function zip_it($survey_id){
		$files = "../../files";
		exec("mkdir $files/$survey_id");
		$sql_file = fopen("$files/$survey_id/sql", 'w');
		fwrite($sql_file, $this->survey_sql($survey_id));
		fwrite($sql_file, $this->items_sql($survey_id));
		fwrite($sql_file, $this->cronjobs_sql($survey_id));
		fwrite($sql_file, $this->rectangles_sql($survey_id));
		fwrite($sql_file, $this->results_sql($survey_id));
		fclose($sql_file);
		exec("rm $files/$survey_id/$survey_id.zip");  //lets make this function idempotent...just in case
		exec("zip -rj $files/$survey_id/$survey_id.zip $files/$survey_id/");
	}
	
	function finalize($survey_id){
		$this->query($this->results_sql($survey_id));
		$this->zip_it($survey_id);
		$this->query("INSERT INTO surveys_users(survey_id, user_id, downloaded) values($survey_id, 1, 0);");
	}
}

?>
